package org.cncitrus.data.db;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.cncitrus.data.common.Params;
import org.cncitrus.data.manager.ParamsManager;

public class WorldYieldAreaConn {
	private Statement stmt;
	private String TABLE_NAME = "orange_w_changliangandmianji";
	public WorldYieldAreaConn() throws SQLException{
		stmt = OrangeConn.getInstance().getStatement();
	}
	
	public String load(String[] year,String[] country,String[] spec) throws Exception{
		
		String sql = "select * from "+TABLE_NAME+" WHERE "+Params.WorldYieldArea.year+" IN "+ParamsManager.Array2String(year)+" AND "+Params.WorldYieldArea.country+" IN "+ParamsManager.Array2String(country)+" AND "+Params.WorldYieldArea.spec+" IN "+ParamsManager.Array2String(spec);
		StringBuffer sb = new StringBuffer();
		sb.append("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
		
		ResultSet rs = stmt.executeQuery(sql);
		rs.last();
		sb.append("<resp count=\""+rs.getRow()+"\">");
		rs.beforeFirst();
		while(rs.next()){
			sb.append("<item year=\""+rs.getString(Params.WorldYieldArea.year).trim()+"\" country=\""+rs.getString(Params.WorldYieldArea.country).trim()+"\" spec =\""+rs.getString(Params.WorldYieldArea.spec).trim()+"\" products =\""+rs.getString(Params.WorldYieldArea.products).trim()+"\" area=\""+rs.getString(Params.WorldYieldArea.area).trim()+"\" />");
		}
		sb.append("</resp>");
		return sb.toString();
	}
	public String insert(String[] year,String[] country,String[] spec,String[] area,String[] products) throws Exception{
		
		StringBuffer sb = new StringBuffer();
		sb.append("INSERT INTO "+TABLE_NAME+" ("+Params.WorldYieldArea.year+","+Params.WorldYieldArea.country+","+Params.WorldYieldArea.spec+","+Params.WorldYieldArea.area+","+Params.WorldYieldArea.products+") VALUES ");		
		
		String content = ParamsManager.getInsertString(year,country,spec,area,products);
		sb.append(content);
		stmt.execute(sb.toString());
		sb = new StringBuffer();
		sb.append("<?xml version=\"1.0\" encoding=\"UTF-8\"?>");
		sb.append("<resp rc=\"0\"/>");		
		return sb.toString();
	}
}
